﻿/*============================================================================
  File:    StoredProcedures.cs

  Summary: A library of Analysis Services stored procedures.

  Date:    June 13, 2005
------------------------------------------------------------------------------
  This file is part of the Microsoft SQL Server Code Samples.

  Copyright (C) Microsoft Corporation.  All rights reserved.

  This source code is intended only as a supplement to Microsoft
  Development Tools and/or on-line documentation.  See these other
  materials for detailed information regarding Microsoft code samples.

  THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY
  KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE
  IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
  PARTICULAR PURPOSE.
============================================================================*/

#region Using directives

using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using Microsoft.AnalysisServices;
using Microsoft.AnalysisServices.AdomdServer;

#endregion

namespace Microsoft.Samples.SqlServer
{
    public sealed class StoredProcedures
    {
        private StoredProcedures()
        {
        }

        /// <summary>
        /// Return the minimum of two numbers
        /// </summary>
        /// <param name="x"></param>
        /// <param name="y"></param>
        /// <returns></returns>
        public static float Minimum(float xValue, float yValue)
        {
            if (xValue < yValue)
            {
                return xValue;
            }
            else
            {
                return yValue;
            }
        }

        /// <summary>
        /// Return the maximum of two numbers
        /// </summary>
        /// <param name="x"></param>
        /// <param name="y"></param>
        /// <returns></returns>
        public static float Maximum(float xValue, float yValue)
        {
            if (xValue > yValue)
            {
                return xValue;
            }
            else
            {
                return yValue;
            }
        }

        /// <summary>
        /// Filter a set of members by an expression
        /// </summary>
        /// <param name="set"></param>
        /// <param name="filterExpression"></param>
        /// <returns></returns>
        [CLSCompliant(false)]
        public static Set FilterSet(Set set, String filterExpression)
        {
            Expression expr = new Expression(filterExpression);

            SetBuilder resultSetBuilder = new SetBuilder();

            foreach (Tuple tuple in set)
            {
                if ((bool)(expr.Calculate(tuple)))
                {
                    resultSetBuilder.Add(tuple);
                }
            }

            return resultSetBuilder.ToSet();
        }

        /// <summary>
        /// Calculate the weighted average of a set
        /// </summary>
        /// <param name="set"></param>
        /// <param name="weightExpression"></param>
        /// <param name="inputExpression"></param>
        /// <returns></returns>
        [CLSCompliant(false)]
        public static float WeightedAverage(Set set, String weightExpression, String inputExpression)
        {
            float expression;
            float weight;

            Expression weightExpr = new Expression(weightExpression);
            Expression inputExpr = new Expression(inputExpression);

            float total = 0;
            float totalWeight = 0;

            foreach (Tuple tuple in set)
            {
                expression = (float)inputExpr.Calculate(tuple);
                weight = (float)weightExpr.Calculate(tuple);

                total += expression * weight;
                totalWeight += weight;
            }

            if (totalWeight > 0)
            {
                return total / totalWeight;
            }
            else
            {
                return 0;
            }
        }

        /// <summary>
        /// Check the current state of the data warehouse and create partitions if necessary
        /// </summary>
        public static void CreatePartitions()
        {
            #region Retrieve order date of last sales transaction

            // Open a connection to the data warehouse
            SqlConnection conn = new SqlConnection("data source=localhost\\yukon;initial catalog=AdventureWorksDW;Integrated Security=SSPI"); //TODO: Properties.Settings.Default.AdventureWorksDW
            conn.Open();

            // Create a command
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;

            // Get the order date key of the last internet sale
            int lastInternetSaleDateKey = 0;
            cmd.CommandText = "select max(OrderDateKey) from FactInternetSales";
            lastInternetSaleDateKey = (int)cmd.ExecuteScalar();

            // Get the order date key of the last reseller sale
            int lastResellerSaleDateKey = 0;
            cmd.CommandText = "select max(OrderDateKey) from FactResellerSales";
            lastResellerSaleDateKey = (int)cmd.ExecuteScalar();

            #endregion

            #region Create partitions

            // Connect to the calling session
            AnalysisServices.Server svr = new AnalysisServices.Server();
            svr.Connect("*");

            // Get the Adventure Works cube
            Database db = svr.Databases.GetByName(Context.CurrentDatabaseName);
            Cube cube = db.Cubes[0];

            MeasureGroup mg;
            int maxOrderDateKey;

            #region Internet Sales measure group

            mg = cube.MeasureGroups.GetByName("Internet Sales");
            maxOrderDateKey = 0;
            foreach (Partition part in mg.Partitions)
            {
                maxOrderDateKey = Math.Max(maxOrderDateKey, Convert.ToInt32(
                    part.Annotations.Find("LastOrderDateKey").Value.Value,
                    System.Globalization.CultureInfo.InvariantCulture));
            }

            if (maxOrderDateKey < lastInternetSaleDateKey)
            {
                Partition part = mg.Partitions.Add("Internet_Sales_"
                    + lastInternetSaleDateKey);
                part.StorageMode = StorageMode.Molap;
                part.Source = new QueryBinding(db.DataSources[0].ID,
                    "SELECT * FROM [dbo].[FactInternetSales] WHERE OrderDateKey > '"
                    + maxOrderDateKey + "' and OrderDateKey <= '" + lastInternetSaleDateKey + "'");
                part.Annotations.Add("LastOrderDateKey", Convert.ToString(lastInternetSaleDateKey,
                    System.Globalization.CultureInfo.InvariantCulture));
                part.Update();
                part.Process();
            }

            #endregion

            #region Reseller Sales measure group

            mg = cube.MeasureGroups.GetByName("Reseller Sales");
            maxOrderDateKey = 0;
            foreach (Partition part in mg.Partitions)
            {
                maxOrderDateKey = Math.Max(maxOrderDateKey, Convert.ToInt32(
                    part.Annotations.Find("LastOrderDateKey").Value.Value,
                    System.Globalization.CultureInfo.InvariantCulture));
            }

            if (maxOrderDateKey < lastResellerSaleDateKey)
            {
                Partition part = mg.Partitions.Add("Reseller_Sales_" + lastResellerSaleDateKey);
                part.StorageMode = StorageMode.Molap;
                part.Source = new QueryBinding(db.DataSources[0].ID,
                    "SELECT * FROM [dbo].[FactResellerSales] WHERE OrderDateKey > '"
                    + maxOrderDateKey + "' and OrderDateKey <= '"
                    + lastResellerSaleDateKey + "'");
                part.Annotations.Add("LastOrderDateKey",
                    Convert.ToString(lastResellerSaleDateKey,
                    System.Globalization.CultureInfo.InvariantCulture));
                part.Update();
                part.Process();
            }

            #endregion

            #region Customers measure group

            mg = cube.MeasureGroups.GetByName("Customers");
            maxOrderDateKey = 0;
            foreach (Partition part in mg.Partitions)
            {
                maxOrderDateKey = Math.Max(maxOrderDateKey, 
                    Convert.ToInt32(part.Annotations.Find("LastOrderDateKey").Value.Value,
                    System.Globalization.CultureInfo.InvariantCulture));
            }

            if (maxOrderDateKey < lastInternetSaleDateKey)
            {
                Partition part = mg.Partitions.Add("Customers_" + lastInternetSaleDateKey);
                part.StorageMode = StorageMode.Molap;
                part.Source = new QueryBinding(db.DataSources[0].ID,
                    "SELECT * FROM [dbo].[FactInternetSales] WHERE OrderDateKey > '"
                    + maxOrderDateKey + "' and OrderDateKey <= '"
                    + lastInternetSaleDateKey + "'");
                part.Annotations.Add("LastOrderDateKey",
                    Convert.ToString(lastInternetSaleDateKey,
                    System.Globalization.CultureInfo.InvariantCulture));
                part.Update();
                part.Process();
            }

            #endregion

            svr.Disconnect();

            #endregion
        }
    }
}
